Currently, it is not possible to pass a formula to the Office API and receive an evaluation of it. There are workarounds, like writing to an empty cell, or writing to a scoped name. Both solutions are not directly pretty. Ideal API: // ...
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getActiveWorksheet();
const evaluatedFormula = await sheet.evaluateFormula(`=SUM(A1:A5,Sheet2!B1:B5)`)
// best possible return value
/*
{
"value": 165,
"type": number,
"cells_included_in_calculation": ["Sheet1!A1:A5","Sheet2!B1:B5"],
"precedents": ["Sheet1!A1:A5","Sheet2!B1:B5"] // can deviate from cells included, in case of formulas like SUMIF / SUMIFS / COUNTIF / COUNTIFS / INDEX / ...
}
=> in case of an error, it could simply return one
*/
}
// ...
... View more